<?php
/**
 * Created by JetBrains PhpStorm.
 * User: zhouyang
 * Date: 13-7-15
 * Time: 下午4:31
 * To change this template use File | Settings | File Templates.
 */
class CatesDB  extends DBReader{
    private $sql_param = 'cate_id, show_type, cate_name, action_url, is_show, left_value, right_value, parent_cate_id';

    //根据cate_id 获取cate 信息
    public function get($cate_id){

        $sql = 'select '.$this->sql_param.' from cates where cate_id = :cate_id';

        return $this->db()->getRow($sql,array('cate_id'=>$cate_id));
    }

    //获取cate 列表<带分页>
    public function getCateList($curr_page,$per_page,$filter = array()){
        $where = ' 1=1 ';
        $bindarr = array();

        if(isset($filter['cate_name'])){
            $where .= ' and cate_name like :cate_name';
            $bindarr['cate_name'] = '%'.$filter['cate_name'].'%';
        }

        if(isset($filter['action'])){
            $where .= ' and action like :action';
            $bindarr['action'] = '%'.$filter['action'].'%';
        }

        $ret = $this->pager($curr_page, $per_page)
            ->ct('select count(cate_id) from cates where ' . $where , $bindarr)
            ->rs('select '.$this->sql_param.' from cates where '.$where.' order by left_value asc',$bindarr);

        return $ret;
    }

    //刷新分类树的预排序
    public function refreshCateTree($right_value,$span = '+2'){
        $lsql = "UPDATE cates SET left_value=left_value".$span." WHERE left_value > :right_value";
        $rsql = "UPDATE cates SET right_value=right_value".$span." WHERE right_value >= :right_value";
        $this->db()->execute($lsql,array('right_value'=>$right_value));
        $this->db()->execute($rsql,array('right_value'=>$right_value));
        return true;
    }

    //由于树的顶部是虚拟的：left_value:0,right_value:max_value
    public function getMaxRightValue(){

        $sql = "select max(right_value) from cates";
        return $this->db()->getOne($sql);
    }

    public function addCate($cate_name,$action,$parent_cate_id,$is_show){

        if($parent_cate_id){
            $cate_info = $this->get($parent_cate_id);
            $left_value = $cate_info['right_value'];
            $right_value = $cate_info['right_value']+1;
            $this->refreshCateTree($cate_info['right_value']);
        }else{
            $max_value = $this->getMaxRightValue();
            $left_value = $max_value + 1;
            $right_value = $max_value + 2;
        }

        $sql = "insert into cates(cate_name,show_type,action_url,is_show,left_value,right_value,parent_cate_id) values(:cate_name,:show_type,:action_url,:is_show,:left_value,:right_value,:parent_cate_id)";

        $bindarr = array(
            'cate_name'=>$cate_name,
            'show_type'=>0,
            'action_url'=>$action,
            'is_show'=>$is_show,
            'left_value'=>$left_value,
            'right_value'=>$right_value,
            'parent_cate_id'=>$parent_cate_id
        );
        $this->db()->execute($sql,$bindarr);
        return $this->db()->lastInsertId();
    }

    //根据左右值获取子分类，包含本身
    public function getCateByLR($left_value,$right_value){

        $sql = 'select '.$this->sql_param.' from cates where left_value >= :left_value and right_value <= :right_value';

        return $this->db()->getAll($sql,array('left_value'=>$left_value,'right_value'=>$right_value));
    }

    public function delAllCate($cate_id){

        $cate_info = $this->get($cate_id);

        $v_width = $cate_info['right_value'] - $cate_info['left_value'] + 1;

        $rs = $this->db()->execute("DELETE FROM cates WHERE left_value BETWEEN :left_value AND :right_value",array('left_value'=>$cate_info['left_value'],'right_value'=>$cate_info['right_value']));

        $this->db()->execute("UPDATE cates SET left_value = left_value - :v_width WHERE left_value > :right_value",array('v_width'=>$v_width,'right_value'=>$cate_info['right_value']));

        $this->db()->execute("UPDATE cates SET right_value = right_value - :v_width WHERE right_value > :right_value",array('v_width'=>$v_width,'right_value'=>$cate_info['right_value']));

        return $rs->rowCount();
    }

    public function updateCate($cate_id,$cate_name,$action,$parent_cate_id,$is_show){

        $sql = "update cates set cate_name = :cate_name,action_url = :action_url,is_show = :is_show,parent_cate_id = :parent_cate_id where cate_id = :cate_id";

        $bindarr = array(
            'cate_name'=>$cate_name,
            'action_url'=>$action,
            'is_show'=>$is_show,
            'cate_id'=>$cate_id,
            'parent_cate_id'=>$parent_cate_id
        );

        return $this->db()->execute($sql,$bindarr);
    }

    //获取用户拥有权限的菜单树
    public function getTree($cate_id = 0,$filter_show = false,$filter_child = false){

        if($cate_id){//查询单个树节点
            $sql = "select left_value,right_value from cates where cate_id = :cate_id";
            $root_info = $this->db()->getRow($sql,array('cate_id'=>$cate_id));
            $sql = 'select '.$this->sql_param.' from cates where left_value >= :left_value and right_value <= :right_value';

            $tree_info = $this->db()->getAll($sql,array('left_value'=>$root_info['left_value'],'right_value'=>$root_info['right_value']));
        }else{//查询所有树节点
            $where = 'where 1=1';

            if($filter_show){
                $where .= ' and is_show = 1';
            }

            if($filter_child){
                $where .= ' and right_value-left_value > 1';
            }

            $sql = 'select '.$this->sql_param.' from cates '.$where.' order by left_value asc';

            $tree_info = $this->db()->getAll($sql);
        }

        return $tree_info;

    }
}